<?php

namespace teach\service;


use Yii;

class Business
{

    /**
     * 查找商城代理信息
     * @param $condition
     * @return array|false
     */
    public function shopInfo($condition)
    {
        $sql = "SELECT  uid AS id, avatar, name, address, update_time, create_time, status, ad FROM mall_business WHERE {$condition}";
        $res = Yii::$app->db->createCommand($sql)->queryOne();

        return $res;
    }

    /**
     * 查找这个率
     * @param $condition
     * @return array|false
     */
    public function shopDiscount($condition)
    {
        $sql = "SELECT * FROM mall_discount WHERE {$condition}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查找正在出售中的商品|下架的商品
     * @param $condition
     * @return false|null|string
     */
    public function shopSale($condition)
    {
        $sql = "SELECT COUNT(*) FROM mall_product WHERE {$condition}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }


    /**
     * 查找用户小于10的商品
     * @param $param
     * @return false|null|string
     */
    public function shopStock($param)
    {
        $sql = "SELECT COUNT(DISTINCT p.id) FROM mall_product as p LEFT JOIN mall_product_specs as s ON p.id = s.pid WHERE s.status = 1 AND p.status = 1 AND s.stock < {$param}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 在上架的商品/下架的商品
     * @param $condition
     * @param $page
     * @param int $pre_page
     * @return array
     */
    public function shopList($condition, $page, $pre_page = 10)
    {
        //统计在卖的商品
        $count = $this->shopSale($condition);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];

        if ($count) {
            $total_page = ceil($count / $pre_page);
            $sql = "SELECT id, aid, mid, cid, title, img,  old_price, no, is_up, count_sale,  parent_id, update_time, create_time FROM mall_product WHERE " . $condition . " ORDER BY sort, create_time DESC LIMIT " . ($page - 1) * $pre_page . " , " . $pre_page;
            $res = Yii::$app->db->createCommand($sql)->queryAll();

            $resData = [];
            foreach ($res as $_key => $_val) {
                $sqlSku = "SELECT * FROM mall_product_specs WHERE pid = {$_val['id']} AND status = 1";
                $sku = Yii::$app->db->createCommand($sqlSku)->queryAll();
                $_val['sku'] = $sku;
                $resData[$_key] = $_val;
            }


            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];
        }
        return $data;

    }

    /**
     * 商品小于10 库存紧张
     * @param $condition
     * @param $page
     * @param int $pre_page
     * @return array
     */
    public function shopStockLow($condition, $page, $pre_page = 10)
    {
        $count = $this->shopStockOther($condition);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $total_page = ceil($count / $pre_page);
            $sql = "SELECT DISTINCT p.id, p.aid, p.mid, p.cid, p.title, p.parent_id, p.img, p.freight, s.stock, p.old_price, p.no, p.is_up, p.count_sale,  p.update_time, p.create_time FROM mall_product as p LEFT JOIN mall_product_specs as s ON p.id = s.pid WHERE p.status = 1 AND s.stock < {$condition} ORDER BY  p.create_time DESC LIMIT " . ($page - 1) * $pre_page . " , " . $pre_page;
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            $resData = [];
            foreach ($res as $_key => $_val) {
                $sqlSku = "SELECT * FROM mall_product_specs WHERE pid = {$_val['id']}";
                $sku = Yii::$app->db->createCommand($sqlSku)->queryAll();
                $_val['sku'] = $sku;
                $resData[$_key] = $_val;
            }

            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];
        }
        return $data;

    }

    /**
     * 统计
     * @param $param
     * @return false|null|string
     */
    public function shopStockOther($param)
    {
        $sql = "SELECT COUNT(DISTINCT p.id) FROM mall_product as p LEFT JOIN mall_product_specs as s ON p.id = s.pid WHERE p.status = 1 AND s.stock < {$param}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }


    /**
     *作用：产生随机字符串，不长于32位
     * @param int $length
     * @return string
     */
    public function createNoncestr($length = 32)
    {
        $chars = "abcdefghijklmnopqrstuvwxyz0123456789";
        $str = "";
        for ($i = 0; $i < $length; $i++) {
            $str .= substr($chars, mt_rand(0, strlen($chars) - 1), 1);
        }
        return $str;
    }

    /**
     * 查找产品父id
     * @param $condition
     * @return false|null|string
     */
    public function productCount($condition)
    {
        $sql = "SELECT COUNT(*) FROM mall_product WHERE {$condition} ";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找分佣的记录
     * @param $condition
     * @return array|false
     */
    public function amountSplit($condition)
    {
        $sql = "SELECT * FROM mall_distribution WHERE {$condition}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 批量插入
     * @param $map
     * @return int
     */
    public function skuBatch($map)
    {
        return Yii::$app->db->createCommand()->batchInsert('mall_product_specs', ['pid', 'value', 'stock', 'price','img', 'create_time'], $map)->execute();
    }

    /**
     * 获取广告图 or other
     * @param $map
     * @return array
     */
    public function shopAd($map)
    {
        $sql = "SELECT title, `desc`, img_url FROM mall_ad_share WHERE {$map} ORDER BY sort DESC";
        return Yii::$app->db->createCommand($sql)->queryAll();
    }

    /**
     * 设置店铺信息
     * @param $map
     * @param $where
     * @return int
     */
    public function shopSet($map, $where, $andMap, $andWhere)
    {
        //$sql = "UPDATE mall_business SET {$map} WHERE {$where}";
        //return Yii::$app->db->createCommand($sql)->execute();
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            $sql = $sql = "UPDATE mall_business SET {$map} WHERE {$where}";
            $db->createCommand($sql)->execute();
            $sqlDistribution = "UPDATE mall_distribution SET {$andMap} WHERE {$andWhere}";
            $db->createCommand($sqlDistribution)->execute();
            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }
    }

    /**
     * 输入 | 修改 订单号 快递公司
     * @param $map
     * @param $where
     * @return int
     */
    public function orderExpress($map, $where)
    {
        $sql = "UPDATE mall_order SET {$map}  WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找其中一条订单记录
     * @param $map
     * @return array|false
     */
    public function orderOne($map)
    {
        $sql = "SELECT * FROM mall_order WHERE {$map}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 分销商
     * @param $where
     * @param $page
     * @param $pre_page
     * @param $distribution
     * @return array
     */
    public function storeTeam($distribution,$where, $page, $pre_page)
    {
        $count = $this->teamCount($where);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];

        if ($count) {
            $total_page = ceil($count / $pre_page);
            $offset = ($page - 1) * $pre_page;
            $sql = "SELECT id, nickname, avatar_url, mall_rname, mall_use, mall_order_count, mall_phone, mall_brother_count,score_total, mall_id, mall_create_time FROM edu_user WHERE {$where} ORDER BY mall_create_time DESC LIMIT {$offset} , {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            //$data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $res];
            $resData = [];
            foreach ($res as $_key => $_val) {
                switch ($_val['score_total'])
                {
                    case $_val['score_total'] >= 0 && $_val['score_total'] < $distribution['three_star_score']:
                        $_val['star'] = $distribution['three_star'];
                        break;
                    case $_val['score_total'] < $distribution['five_star_score']:
                        $_val['star'] = $distribution['three_star'];
                        break;
                    case $_val['score_total'] < $distribution['six_star_score']:
                        $_val['star'] = $distribution['five_star'];
                        break;
                    case $_val['score_total'] < $distribution['seven_star_score']:
                        $_val['star'] = $distribution['six_star'];
                        break;
                    case $_val['score_total'] >= $distribution['seven_star_score']:
                        $_val['star'] = $distribution['seven_star'];
                        break;
                    default:
                        $_val['star'] = 3;
                }
                //实施统计此用户的购买订单量
                $order_count_sql = "SELECT COUNT(*) FROM mall_order WHERE uid = {$_val['id']} AND (status = 1 OR status = 2 OR status = 3)";
                $_val['mall_order_count'] = Yii::$app->db->createCommand($order_count_sql)->queryScalar();
                $resData[$_key] = $_val;
            }

            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];
        }

        return $data;
    }

    /**
     * 统计团队总人数
     * @param $where
     * @return false|null|string
     */
    public function teamCount($where)
    {
        $sql = "SELECT COUNT(*) FROM edu_user WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 将商品设置为status = -1
     * @param $where
     * @return int
     */
    public function productDelete($where)
    {
        $sql = "UPDATE mall_product SET status = -1 WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 产品下架
     * @param $where
     * @return int
     */
    public function productDown($where)
    {
        $sql = "UPDATE mall_product SET is_up = -1 WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 上架商品
     * @param $where
     * @return int
     */
    public function productUp($where)
    {
        $sql = "UPDATE mall_product SET is_up = 1 WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找其中一个商家
     * @param $where
     * @return array|false
     */
    public function storeOne($where)
    {
        $sql = "SELECT * FROM edu_user WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取提现规则
     * @param $where
     * @return array|false
     */
    public function moneyRuler($where)
    {
        $sql = "SELECT content FROM mall_editor WHERE {$where} ";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取规则
     * @param $mid
     * @return array|false
     */
    public function shopRule($mid)
    {
        $sql = "SELECT id, mid, state, discount, low_total_fee, logistic_fee FROM mall_distribution WHERE mid = {$mid}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取一条用户记录
     * @param $where
     * @return array|false
     */
    public function userOne($where)
    {
        $sql = "SELECT * FROM edu_user WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 查询一条佣金规则记录
     * @param $filter
     * @return array|false
     */
    public function distribution($filter)
    {
        $sql = "SELECT * FROM mall_distribution WHERE {$filter}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 通过修改score_total值而修改用户星级
     * @param $map
     * @param $where
     * @param $record
     * @return int
     */
    public function userStarChange($map, $where, $record)
    {
        //$sql = "UPDATE edu_user SET {$map} WHERE {$where}";
        //return Yii::$app->db->createCommand($sql)->execute();

        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            $db->createCommand()->insert('mall_star_change', $record)->execute();
            $sql = "UPDATE edu_user SET {$map} WHERE {$where}";
            $db->createCommand($sql)->execute();
            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }
    }

    /**
     * 查找申请退款的订单跟支付表记录
     * @param $where
     * @return array|false
     */
    public function orderRefundOne($where)
    {
        $sql = "SELECT orders.id AS oid, orders.openid,orders.mid, orders.uid, orders.out_trade_no, orders.total_fee, orders.status AS order_status, pay.transaction_id, pay.status AS pay_status, pay.id AS p_id FROM mall_order AS orders LEFT JOIN mall_pay AS pay ON orders.out_trade_no = pay.out_trade_no WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 将订单修改为确认退款或者拒绝退款
     * @param $map
     * @param $where
     * @return int
     */
    public function orderRefundStatus($map, $where)
    {
        $sql = "UPDATE mall_order SET {$map} WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 退款后更新记录状态 并记录返回的信息
     * @param $pay_id
     * @param $oid
     * @param $out_trade_no
     * @param $result
     * @return bool
     */
    public function orderBatchStatus($pay_id, $oid, $out_trade_no, $result)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            //订单表
            $sqlTotal = "UPDATE mall_order SET status = -3 WHERE id = {$oid}";
            $db->createCommand($sqlTotal)->execute();
            //支付表记录分表
            $sqlList = "UPDATE mall_pay SET status = -2,info = '{$result}' WHERE id = {$pay_id}";
            $db->createCommand($sqlList)->execute();
            //积分记录表
            $sqlList = "UPDATE mall_bill SET status = -1 WHERE out_trade_no = '{$out_trade_no}'";
            $db->createCommand($sqlList)->execute();
            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }
    }

    /**
     * 获取一条申请退款记录
     * @param $where
     * @return array|false
     */
    public function refundOne($where)
    {
        $sql = "SELECT * FROM mall_product_refund_reason WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    public function orderPayStatus($p_id)
    {
        $sql = "UPDATE mall_pay SET status = 1 WHERE id = {$p_id}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

}